﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Threading.Tasks;
using QQTools.Data.Models.SendGroupMsg;
using QQTools.Logging;

namespace QQTools.Data.Repositories.SendGroupMessage
{
    /// <summary>
    /// z群消息任务QQ持久化仓储
    /// </summary>
    public class SendGroupMsgQQRepository
    {

        private readonly string _connectionString;
        public SendGroupMsgQQRepository(string connectionString)
        {
            _connectionString = connectionString;
        }

        /// <summary>
        /// 获取所有任务记录
        /// </summary>
        public async Task<List<SendGroupMsgQQModel>> GetAllAsync()
        {
            var tasks = new List<SendGroupMsgQQModel>();
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = "SELECT * FROM send_group_msg_qq";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                tasks.Add(MapToTask(reader));
                            }
                        }
                    }
                }
                LogManager.Info("获取所有 SendGroupMsgQQModels 成功");
            }
            catch (Exception ex)
            {
                LogManager.Error("获取所有 SendGroupMsgQQModels 失败", ex);
            }
            return tasks;
        }

        /// <summary>
        /// 根据 Id 获取任务记录
        /// </summary>
        public async Task<SendGroupMsgQQModel> GetByIdAsync(int id)
        {
            SendGroupMsgQQModel task = null;
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = "SELECT * FROM send_group_msg_qq WHERE id = @id";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@id", id);
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            if (await reader.ReadAsync())
                            {
                                task = MapToTask(reader);
                            }
                        }
                    }
                }
                LogManager.Info($"根据Id={id}获取 SendGroupMsgQQModels 成功");
            }
            catch (Exception ex)
            {
                LogManager.Error($"根据Id={id}获取 SendGroupMsgQQModels 失败", ex);
            }
            return task;
        }

        /// <summary>
        /// 根据 QQ 获取任务记录（可能存在多条）
        /// </summary>
        public async Task<List<SendGroupMsgQQModel>> GetByQQAsync(string qq)
        {
            var tasks = new List<SendGroupMsgQQModel>();
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = "SELECT * FROM send_group_msg_qq WHERE qq = @qq";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@qq", qq);
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                tasks.Add(MapToTask(reader));
                            }
                        }
                    }
                }
                LogManager.Info($"根据QQ={qq}获取 SendGroupMsgQQModels 成功");
            }
            catch (Exception ex)
            {
                LogManager.Error($"根据QQ={qq}获取 SendGroupMsgQQModels 失败", ex);
            }
            return tasks;
        }

        /// <summary>
        /// 删除任务记录
        /// </summary>
        public async Task<bool> DeleteAsync(int id)
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = "DELETE FROM send_group_msg_qq WHERE id = @id";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@id", id);
                        int result = await command.ExecuteNonQueryAsync();
                        LogManager.Info($"删除 SendGroupMsgQQModels id={id} 成功");
                        return result > 0;
                    }
                }
            }
            catch (Exception ex)
            {
                LogManager.Error($"删除 SendGroupMsgQQModels id={id} 失败", ex);
                return false;
            }
        }

        /// <summary>
        /// 清空任务表
        /// </summary>
        public async Task<bool> ClearTableAsync()
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = "DELETE FROM send_group_msg_qq";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        await command.ExecuteNonQueryAsync();
                    }
                }
                LogManager.Info("清空 send_group_msg_qq 表成功");
                return true;
            }
            catch (Exception ex)
            {
                LogManager.Error("清空 send_group_msg_qq 表失败", ex);
                return false;
            }
        }

        /// <summary>
        /// 更新任务记录
        /// </summary>
        public async Task<bool> UpdateAsync(SendGroupMsgQQModel task)
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = @"UPDATE send_group_msg_qq 
                                   SET qq = @qq, send_count = @send_count, failed_count = @failed_count, resource_index = @resource_index, update_time = @update_time 
                                   WHERE id = @id";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@qq", task.QQ);
                        command.Parameters.AddWithValue("@send_count", task.SendCount);
                        command.Parameters.AddWithValue("@failed_count", task.FailedCount);
                        command.Parameters.AddWithValue("@resource_index", task.ResourceIndex);
                        command.Parameters.AddWithValue("@update_time", DateTime.Now);
                        command.Parameters.AddWithValue("@id", task.Id);
                        int result = await command.ExecuteNonQueryAsync();
                        LogManager.Info($"更新 SendGroupMsgQQModels id={task.Id} 成功");
                        return result > 0;
                    }
                }
            }
            catch (Exception ex)
            {
                LogManager.Error($"更新 SendGroupMsgQQModels id={task.Id} 失败", ex);
                return false;
            }
        }



        /// <summary>
        /// 添加任务记录
        /// </summary>
        public async Task<bool> AddAsync(SendGroupMsgQQModel task)
        {
            try
            {
                task.CreateTime = DateTime.Now;
                task.UpdateTime = DateTime.Now;
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = @"INSERT INTO send_group_msg_qq (qq, send_count, failed_count, resource_index, create_time, update_time)
                                   VALUES (@qq, @send_count, @failed_count, @resource_index, @create_time, @update_time)";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@qq", task.QQ);
                        command.Parameters.AddWithValue("@send_count", task.SendCount);
                        command.Parameters.AddWithValue("@failed_count", task.FailedCount);
                        command.Parameters.AddWithValue("@resource_index", task.ResourceIndex);
                        command.Parameters.AddWithValue("@create_time", task.CreateTime);
                        command.Parameters.AddWithValue("@update_time", task.UpdateTime);
                        int result = await command.ExecuteNonQueryAsync();
                        LogManager.Info($"添加 SendGroupMsgQQModels QQ={task.QQ} 成功");
                        return result > 0;
                    }
                }
            }
            catch (Exception ex)
            {
                LogManager.Error("添加 SendGroupMsgQQModels 失败", ex);
                return false;
            }
        }

        /// <summary>
        /// 添加或更新任务记录：如果根据 QQ 已存在记录则更新，否则添加
        /// </summary>
        public async Task<bool> AddOrUpdateAsync(SendGroupMsgQQModel task)
        {
            try
            {
                // 使用事务保证原子性
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    using (var transaction = connection.BeginTransaction())
                    {
                        string sqlCheck = "SELECT id FROM send_group_msg_qq WHERE qq = @qq";
                        using (var checkCmd = new SQLiteCommand(sqlCheck, connection, transaction))
                        {
                            checkCmd.Parameters.AddWithValue("@qq", task.QQ);
                            var existingId = await checkCmd.ExecuteScalarAsync();
                            if (existingId != null)
                            {
                                // 更新记录
                                task.Id = Convert.ToInt32(existingId);
                                string sqlUpdate = @"UPDATE send_group_msg_qq 
                                                     SET send_count = @send_count, failed_count = @failed_count, , resource_index = @resource_index update_time = @update_time 
                                                     WHERE id = @id";
                                using (var updateCmd = new SQLiteCommand(sqlUpdate, connection, transaction))
                                {
                                    updateCmd.Parameters.AddWithValue("@send_count", task.SendCount);
                                    updateCmd.Parameters.AddWithValue("@failed_count", task.FailedCount);
                                    updateCmd.Parameters.AddWithValue("@resource_index", task.ResourceIndex);
                                    updateCmd.Parameters.AddWithValue("@update_time", DateTime.Now);
                                    updateCmd.Parameters.AddWithValue("@id", task.Id);
                                    await updateCmd.ExecuteNonQueryAsync();
                                }
                                LogManager.Info($"更新 SendGroupMsgQQModels QQ={task.QQ} 成功");
                            }
                            else
                            {
                                // 插入记录
                                task.CreateTime = DateTime.Now;
                                task.UpdateTime = DateTime.Now;
                                string sqlInsert = @"INSERT INTO send_group_msg_qq (qq, send_count, failed_count, resource_index, create_time, update_time)
                                                     VALUES (@qq, @send_count, @failed_count, @resource_index, @create_time, @update_time)";
                                using (var insertCmd = new SQLiteCommand(sqlInsert, connection, transaction))
                                {
                                    insertCmd.Parameters.AddWithValue("@qq", task.QQ);
                                    insertCmd.Parameters.AddWithValue("@send_count", task.SendCount);
                                    insertCmd.Parameters.AddWithValue("@failed_count", task.FailedCount);
                                    insertCmd.Parameters.AddWithValue("@resource_index", task.ResourceIndex);
                                    insertCmd.Parameters.AddWithValue("@create_time", task.CreateTime);
                                    insertCmd.Parameters.AddWithValue("@update_time", task.UpdateTime);
                                    await insertCmd.ExecuteNonQueryAsync();
                                }
                                LogManager.Info($"添加 SendGroupMsgQQModels QQ={task.QQ} 成功");
                            }
                        }
                        transaction.Commit();
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                LogManager.Error("AddOrUpdate SendGroupMsgQQModels 失败", ex);
                return false;
            }
        }

        /// <summary>
        /// 根据 QQ 让 send_count 自增 1
        /// </summary>
        public async Task<bool> IncrementSendCountByQQAsync(string qq)
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = @"UPDATE send_group_msg_qq 
                                   SET send_count = send_count + 1, update_time = @update_time 
                                   WHERE qq = @qq";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@update_time", DateTime.Now);
                        command.Parameters.AddWithValue("@qq", qq);
                        int result = await command.ExecuteNonQueryAsync();
                        LogManager.Info($"自增 SendGroupMsgQQModels QQ={qq} 的 send_count 成功");
                        return result > 0;
                    }
                }
            }
            catch (Exception ex)
            {
                LogManager.Error($"自增 SendGroupMsgQQModels QQ={qq} 的 send_count 失败", ex);
                return false;
            }
        }

        /// <summary>
        /// 根据 QQ 让 failed_count 自增 1
        /// </summary>
        public async Task<bool> IncrementFailedCountByQQAsync(string qq)
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = @"UPDATE send_group_msg_qq 
                                   SET failed_count = failed_count + 1, update_time = @update_time 
                                   WHERE qq = @qq";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@update_time", DateTime.Now);
                        command.Parameters.AddWithValue("@qq", qq);
                        int result = await command.ExecuteNonQueryAsync();
                        LogManager.Info($"自增 SendGroupMsgQQModels QQ={qq} 的 failed_count 成功");
                        return result > 0;
                    }
                }
            }
            catch (Exception ex)
            {
                LogManager.Error($"自增 SendGroupMsgQQModels QQ={qq} 的 failed_count 失败", ex);
                return false;
            }
        }

        /// <summary>
        /// 根据 QQ 让 资源下标resource_index 自增 1
        /// </summary>
        public async Task<bool> IncrementResourceIndexByQQAsync(string qq)
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = @"UPDATE send_group_msg_qq 
                                   SET resource_index = resource_index + 1, update_time = @update_time 
                                   WHERE qq = @qq";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@update_time", DateTime.Now);
                        command.Parameters.AddWithValue("@qq", qq);
                        int result = await command.ExecuteNonQueryAsync();
                        LogManager.Info($"自增 SendGroupMsgQQModels QQ={qq} 的 resource_index 成功");
                        return result > 0;
                    }
                }
            }
            catch (Exception ex)
            {
                LogManager.Error($"自增 SendGroupMsgQQModels QQ={qq} 的 resource_index 失败", ex);
                return false;
            }
        }

        /// <summary>
        /// 将 SqlDataReader 映射到 SendGroupMsgQQModels 对象
        /// </summary>
        private SendGroupMsgQQModel MapToTask(System.Data.Common.DbDataReader reader)
        {
            return new SendGroupMsgQQModel
            {
                Id = reader.GetInt32(reader.GetOrdinal("id")),
                QQ = reader.GetString(reader.GetOrdinal("qq")),
                SendCount = reader.GetInt32(reader.GetOrdinal("send_count")),
                FailedCount = reader.GetInt32(reader.GetOrdinal("failed_count")),
                ResourceIndex = reader.GetInt32(reader.GetOrdinal("resource_index")),
                CreateTime = reader.GetDateTime(reader.GetOrdinal("create_time")),
                UpdateTime = reader.GetDateTime(reader.GetOrdinal("update_time"))
            };
        }
    }
}
